package qldc.pm.tiensu.service.persistence;

import java.util.Date;
import java.util.Iterator;
import java.util.List;

import qldc.pm.qtht.dmdc.utils.StringUtils;
import qldc.pm.tiensu.constants.TrangThaiTrichLucEnum;
import qldc.pm.tiensu.model.YeuCauTrichLuc;
import qldc.pm.tiensu.model.impl.YeuCauTrichLucImpl;

import com.liferay.portal.kernel.dao.orm.QueryPos;
import com.liferay.portal.kernel.dao.orm.QueryUtil;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.portal.kernel.dao.orm.Type;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.util.OrderByComparator;
import com.liferay.portal.kernel.util.StringPool;
import com.liferay.portal.kernel.util.StringUtil;
import com.liferay.portal.kernel.util.Validator;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;

public class YeuCauTrichLucFinderImpl extends
BasePersistenceImpl<YeuCauTrichLuc> implements YeuCauTrichLucFinder {
	private static Log _log = LogFactoryUtil.getLog(YeuCauTrichLucFinderImpl.class);

public static final String FINDBYSDDCN_NYCTL_CQ_CMND_DTTL_SHK_THOIGIAN = YeuCauTrichLucFinder.class
	.getName() + ".findBySDDCN_NYCTL_CQ_CMND_DTTL_SHK_ThoiGian";

public static final String COUNTBYSDDCN_NYCTL_CQ_CMND_DTTL_SHK_THOIGIAN = YeuCauTrichLucFinder.class
	.getName() + ".countBySDDCN_NYCTL_CQ_CMND_DTTL_SHK_ThoiGian";


public static final String COUNTBYCQ_NGUOITHUCHIENTAOTRICHLUC_THOIGIAN_VUNGDIALY = YeuCauTrichLucFinder.class
	.getName() + ".countByCQ_NguoiThucHienTaoTrichLuc_ThoiGian_VungDiaLy";


public static final String FINDBYCQ_NGUOITHUCHIENTAOTRICHLUC_THOIGIAN_VUNGDIALY = YeuCauTrichLucFinder.class
	.getName() + ".findByCQ_NguoiThucHienTaoTrichLuc_ThoiGian_VungDiaLy";

@SuppressWarnings("unchecked")
public List<YeuCauTrichLuc> findByCQ_NguoiThucHienTaoTrichLuc_ThoiGian_VungDiaLy(		
		String coQuanYeuCauTrichLuc,
		String nguoiTaoTrichLuc,
		long tinhIDCanBoTaoTrichLuc,
		long quanHuyenIDCanBoTaoTrichLuc, 
		long phuongXaIDCanBoTaoTrichLuc,
		Date tuNgayTaoYeuCau, Date denNgayTaoYeuCau, int start, int end, OrderByComparator orderByComparator)
	throws SystemException {

	Session session = null;

	try {
		session = openSession();


		String sql = CustomSQLUtil.get(FINDBYCQ_NGUOITHUCHIENTAOTRICHLUC_THOIGIAN_VUNGDIALY);

		
		if (Validator.isNull(tuNgayTaoYeuCau) && Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(tuNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
		if (phuongXaIDCanBoTaoTrichLuc <= 0){
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.noiThuongTruDoiTuongTrichLucPhuongXaId = ?", StringPool.BLANK);
		}
		if (quanHuyenIDCanBoTaoTrichLuc <= 0){
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.noiThuongTruDoiTuongTrichLucQuanHuyenId = ?", StringPool.BLANK);
		}

		if (tinhIDCanBoTaoTrichLuc <= 0){
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.noiThuongTruDoiTuongTrichLucPhuongXaId = ?", StringPool.BLANK);
		}
		
		
		sql = CustomSQLUtil.replaceAndOperator(sql, true);

		sql = CustomSQLUtil.replaceOrderBy(sql, orderByComparator);
		SQLQuery q = session.createSQLQuery(sql);
		q.addEntity("YeuCauTrichLuc", YeuCauTrichLucImpl.class);

		QueryPos qpos = QueryPos.getInstance(q);


		qpos.add(StringPool.PERCENT + coQuanYeuCauTrichLuc + StringPool.PERCENT);
		qpos.add(coQuanYeuCauTrichLuc);

		
		if (Validator.isNotNull(tuNgayTaoYeuCau)) {
			qpos.add(tuNgayTaoYeuCau);
		}

		if (Validator.isNotNull(denNgayTaoYeuCau)) {
			qpos.add(denNgayTaoYeuCau);
		}
		qpos.add(StringPool.PERCENT + nguoiTaoTrichLuc + StringPool.PERCENT);
		qpos.add(nguoiTaoTrichLuc);

		
		if (phuongXaIDCanBoTaoTrichLuc > 0) {
			qpos.add(phuongXaIDCanBoTaoTrichLuc);
		}
		if (quanHuyenIDCanBoTaoTrichLuc > 0) {
			qpos.add(quanHuyenIDCanBoTaoTrichLuc);
		}
		if (tinhIDCanBoTaoTrichLuc > 0) {
			qpos.add(tinhIDCanBoTaoTrichLuc);
		}

		List<YeuCauTrichLuc> data = (List<YeuCauTrichLuc>) QueryUtil.list(q, getDialect(),start, end);
		return data;
	} catch (Exception e) {
		throw new SystemException(e);
	} finally {
		closeSession(session);
	}

}


@SuppressWarnings("unchecked")
public int countByCQ_NguoiThucHienTaoTrichLuc_ThoiGian_VungDiaLy(
		String coQuanYeuCauTrichLuc,
		String nguoiTaoTrichLuc,
		long tinhIDCanBoTaoTrichLuc,
		long quanHuyenIDCanBoTaoTrichLuc, 
		long phuongXaIDCanBoTaoTrichLuc,
		Date tuNgayTaoYeuCau, Date denNgayTaoYeuCau)
	throws SystemException {

	Session session = null;

	try {
		session = openSession();



		String sql = CustomSQLUtil.get(COUNTBYCQ_NGUOITHUCHIENTAOTRICHLUC_THOIGIAN_VUNGDIALY);

		if (Validator.isNull(tuNgayTaoYeuCau) && Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(tuNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
		if (phuongXaIDCanBoTaoTrichLuc <= 0){
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.noiThuongTruDoiTuongTrichLucPhuongXaId = ?", StringPool.BLANK);
		}
		if (quanHuyenIDCanBoTaoTrichLuc <= 0){
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.noiThuongTruDoiTuongTrichLucQuanHuyenId = ?", StringPool.BLANK);
		}

		if (tinhIDCanBoTaoTrichLuc <= 0){
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.noiThuongTruDoiTuongTrichLucTinhId = ?", StringPool.BLANK);
		}
		
		sql = CustomSQLUtil.replaceAndOperator(sql, true);
		SQLQuery q = session.createSQLQuery(sql);
		q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
		QueryPos qpos = QueryPos.getInstance(q);
		



		qpos.add(StringPool.PERCENT + coQuanYeuCauTrichLuc + StringPool.PERCENT);
		qpos.add(coQuanYeuCauTrichLuc);

		
		if (Validator.isNotNull(tuNgayTaoYeuCau)) {
			qpos.add(tuNgayTaoYeuCau);
		}

		if (Validator.isNotNull(denNgayTaoYeuCau)) {
			qpos.add(denNgayTaoYeuCau);
		}
		qpos.add(StringPool.PERCENT + nguoiTaoTrichLuc + StringPool.PERCENT);
		qpos.add(nguoiTaoTrichLuc);

		
		if (phuongXaIDCanBoTaoTrichLuc > 0) {
			qpos.add(phuongXaIDCanBoTaoTrichLuc);
		}
		if (quanHuyenIDCanBoTaoTrichLuc > 0) {
			qpos.add(quanHuyenIDCanBoTaoTrichLuc);
		}
		if (tinhIDCanBoTaoTrichLuc > 0) {
			qpos.add(tinhIDCanBoTaoTrichLuc);
		}

		Iterator<Long> iterator = q.list().iterator();
		if (iterator.hasNext()) {
			Long count = iterator.next();
			if (Validator.isNotNull(count)) {
				return count.intValue();
			}
		}
	} catch (Exception e) {
		throw new SystemException(e);
	} finally {
		closeSession(session);
	}
	return 0;

}



@SuppressWarnings("unchecked")
public List<YeuCauTrichLuc> findBySDDCN_NYCTL_CQ_CMND_DTTL_SHK_ThoiGian(
		int trangThaiTrichLuc,
		String soDinhDanhCaNhanDoiTuongTrichLuc,
		String hoVaTenNguoiYeuCauTrichLuc,String coQuanYeuCauTrichLuc,
		String soCMNDDoiTuongTrichLuc,
		String hoVaTenDoiTuongTrichLuc, String soSoHoKhauDoiTuongTrichLuc, Date tuNgayTaoYeuCau, Date denNgayTaoYeuCau, int start, int end, OrderByComparator orderByComparator)
	throws SystemException {

	Session session = null;

	try {
		session = openSession();


		String sql = CustomSQLUtil.get(FINDBYSDDCN_NYCTL_CQ_CMND_DTTL_SHK_THOIGIAN);

		
		if (trangThaiTrichLuc == TrangThaiTrichLucEnum.TAT_CA.getValue()) {
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.daTrichLuc = ?",StringPool.BLANK);
		}
		if (Validator.isNull(tuNgayTaoYeuCau) && Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(tuNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
	
		
		sql = CustomSQLUtil.replaceAndOperator(sql, true);

		sql = CustomSQLUtil.replaceOrderBy(sql, orderByComparator);

		SQLQuery q = session.createSQLQuery(sql);

		q.addEntity("YeuCauTrichLuc", YeuCauTrichLucImpl.class);

		QueryPos qpos = QueryPos.getInstance(q);


		qpos.add(StringPool.PERCENT + soDinhDanhCaNhanDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(soDinhDanhCaNhanDoiTuongTrichLuc);
		

		qpos.add(StringPool.PERCENT + hoVaTenNguoiYeuCauTrichLuc + StringPool.PERCENT);
		qpos.add(hoVaTenNguoiYeuCauTrichLuc);

		qpos.add(StringPool.PERCENT + coQuanYeuCauTrichLuc + StringPool.PERCENT);
		qpos.add(coQuanYeuCauTrichLuc);

		qpos.add(StringPool.PERCENT + soCMNDDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(soCMNDDoiTuongTrichLuc);
		

		qpos.add(StringPool.PERCENT + hoVaTenDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(hoVaTenDoiTuongTrichLuc);

		qpos.add(StringPool.PERCENT + soSoHoKhauDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(soSoHoKhauDoiTuongTrichLuc);

		
		if (Validator.isNotNull(tuNgayTaoYeuCau)) {
			qpos.add(tuNgayTaoYeuCau);
		}

		if (Validator.isNotNull(denNgayTaoYeuCau)) {
			qpos.add(denNgayTaoYeuCau);
		}
		if (trangThaiTrichLuc != TrangThaiTrichLucEnum.TAT_CA.getValue()) {
			qpos.add(trangThaiTrichLuc);
		}
		

		return (List<YeuCauTrichLuc>) QueryUtil.list(q, getDialect(),start, end);
	} catch (Exception e) {
		throw new SystemException(e);
	} finally {
		closeSession(session);
	}

}


@SuppressWarnings("unchecked")
public int countBySDDCN_NYCTL_CQ_CMND_DTTL_SHK_ThoiGian(int trangThaiTrichLuc,
		String soDinhDanhCaNhanDoiTuongTrichLuc,
		String hoVaTenNguoiYeuCauTrichLuc,String coQuanYeuCauTrichLuc,
		String soCMNDDoiTuongTrichLuc,
		String hoVaTenDoiTuongTrichLuc, String soSoHoKhauDoiTuongTrichLuc, Date tuNgayTaoYeuCau, Date denNgayTaoYeuCau)
	throws SystemException {

	Session session = null;

	try {
		session = openSession();



		String sql = CustomSQLUtil.get(COUNTBYSDDCN_NYCTL_CQ_CMND_DTTL_SHK_THOIGIAN);

		
		if (trangThaiTrichLuc == TrangThaiTrichLucEnum.TAT_CA.getValue()) {
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.daTrichLuc = ?",StringPool.BLANK);
		}
		if (Validator.isNull(tuNgayTaoYeuCau) && Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
			sql = StringUtil.replace(sql,"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(tuNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc >= ?", StringPool.BLANK);
		}
		else if (Validator.isNull(denNgayTaoYeuCau)) {
			sql = StringUtil.replace(sql,
					"AND TS_YeuCauTrichLuc.ngayLapTrichLuc <= ?", StringPool.BLANK);
		}
	
		
		sql = CustomSQLUtil.replaceAndOperator(sql, true);

		SQLQuery q = session.createSQLQuery(sql);

		q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

		QueryPos qpos = QueryPos.getInstance(q);
		



		qpos.add(StringPool.PERCENT + soDinhDanhCaNhanDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(soDinhDanhCaNhanDoiTuongTrichLuc);
		

		qpos.add(StringPool.PERCENT + hoVaTenNguoiYeuCauTrichLuc + StringPool.PERCENT);
		qpos.add(hoVaTenNguoiYeuCauTrichLuc);
		

		qpos.add(StringPool.PERCENT + coQuanYeuCauTrichLuc + StringPool.PERCENT);
		qpos.add(coQuanYeuCauTrichLuc);
		
		qpos.add(StringPool.PERCENT + soCMNDDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(soCMNDDoiTuongTrichLuc);
		

		qpos.add(StringPool.PERCENT + hoVaTenDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(hoVaTenDoiTuongTrichLuc);

		qpos.add(StringPool.PERCENT + soSoHoKhauDoiTuongTrichLuc + StringPool.PERCENT);
		qpos.add(soSoHoKhauDoiTuongTrichLuc);
		
		if (Validator.isNotNull(tuNgayTaoYeuCau)) {
			qpos.add(tuNgayTaoYeuCau);
		}

		if (Validator.isNotNull(denNgayTaoYeuCau)) {
			qpos.add(denNgayTaoYeuCau);
		}
		if (trangThaiTrichLuc != TrangThaiTrichLucEnum.TAT_CA.getValue()) {
			qpos.add(trangThaiTrichLuc);
		}
		
		Iterator<Long> iterator = q.list().iterator();
		if (iterator.hasNext()) {
			Long count = iterator.next();

			if (Validator.isNotNull(count)) {
				return count.intValue();
			}
		}
	} catch (Exception e) {
		throw new SystemException(e);
	} finally {
		closeSession(session);
	}
	return 0;

}

}
